package ProjectMgtSystem.Utilities;

import java.util.ArrayList;
import java.sql.DriverManager;

import java.sql.*;
import javax.sql.*;
//////////////////////////////////////////////////
//
public final class DBHelper {
    // <editor-fold defaultstate="collapsed" desc="define parameter">

    private static DBHelper dbHelper;
    private Connection conn = null;
    private static final String DEFAULT_DRIVER = "com.mysql.jdbc.Driver";
    private static final String DEFAULT_URL = "jdbc:mysql://localhost:3306/";
    private static final String DEFAULT_DBNAME = "projectmanagement";
    private static final String DEFAULT_USERNAME = "root";
    private static final String DEFAULT_PASSWORD = "root";
    private String url;
    private String driver;
    private String dbname;
    private String username;
    private String password;
    CallableStatement proc_stmt = null;
    ResultSet rs = null;
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="initial function">
    /**
     * constructor
     */
    public DBHelper() {
        this.setDriver(DEFAULT_DRIVER);
        this.setUrl(DEFAULT_URL);
        this.setDbname(DEFAULT_DBNAME);
        this.setUsername(DEFAULT_USERNAME);
        this.setPassword(DEFAULT_PASSWORD);
    }

    /**
     * 
     * @return 
     */
    public static DBHelper getInstance() {
        if (dbHelper == null) {
            dbHelper = new DBHelper();
        }
        return dbHelper;
    }

    /**
     * initial connection to DB
     * @return 
     */
    public boolean initConnection() {
        try {
            Class.forName(getDriver());
            conn = DriverManager.getConnection(getUrl() + getDbname(), getUsername(), getPassword());
            return true;
        } catch (Exception ex) {
            return false;
        }
    }

    public void closeConnection() {
        try {

            rs.close();
            proc_stmt.close();
            conn.close();
        } catch (Exception e) {
        }
    }
// </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="define functions to execute sql">
    /**
     * @return the executeQueryProc return ResultSet
     * @param procName: name of procedure
     * @param ArrayList<String>: names of parameter in procedure
     */
    public ResultSet executeQueryProc(String procName, ArrayList<String> arrString) {
        ArrayList<String> myArray = new ArrayList<String>();
        String callProc = "call " + procName;
        try {
            // create parameter and value to parameter
            if (arrString != null) {
                callProc = callProc + "(";

                for (int i = 0; i < arrString.size() - 1; i++) {
                    callProc = callProc + "?,";
                }
                callProc = callProc + "?)";

                proc_stmt = conn.prepareCall(callProc);

                for (int i = 1; i <= arrString.size(); i++) {
                    proc_stmt.setString(i, arrString.get(i - 1));
                }
            } else {
                proc_stmt = conn.prepareCall(callProc);
            }
            rs = proc_stmt.executeQuery();

            if (rs == null) {
                return null;
            } else {
                return rs;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
            return null;
        }
    }   

    /**
     * @return the executeUpdateProcReturn return String
     * @param procName: name of procedure
     * @param ArrayList<String>: names of "IN" parameter in procedure
     */
    public String executeUpdateProcReturn(String procName, ArrayList<String> arrString) {
        String strOutParam = "";
        CallableStatement proc_stmt = null;
        String callProc = "call " + procName + "(";
        try {
            if (arrString != null) {
                for (int i = 0; i < arrString.size(); i++) {
                    callProc = callProc + "?,";
                }
                callProc = callProc + "?)";

                proc_stmt = conn.prepareCall(callProc);
                for (int i = 1; i <= arrString.size(); i++) {
                    proc_stmt.setString(i, arrString.get(i - 1));
                }
                proc_stmt.registerOutParameter(arrString.size() + 1, Types.VARCHAR);
            } else {
                callProc = callProc + "?)";
                proc_stmt.registerOutParameter(1, Types.VARCHAR);
            }

            proc_stmt.executeUpdate();
            strOutParam = proc_stmt.getString("Message");
            return strOutParam;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return "-1";
        }
    }
///*
//    /**
//     * 
//     * @param procName
//     * @param arrString 
//     */
//    public void executeUpdateProc(String procName, ArrayList<String> arrString) {
//        CallableStatement proc_stmt = null;
//        String callProc = "call " + procName + "(";
//        try {
//            if (arrString != null) {
//                for (int i = 0; i < arrString.size() - 1; i++) {
//                    callProc = callProc + "?,";
//                }
//                callProc = callProc + "?)";
//
//                proc_stmt = conn.prepareCall(callProc);
//                for (int i = 1; i <= arrString.size(); i++) {
//                    proc_stmt.setString(i, arrString.get(i - 1));
//                }
//            } 
//            proc_stmt.execute();
//
//            proc_stmt.close();
//
//        } catch (SQLException ex) {
//            ex.printStackTrace();
//        }
//    }
//*/

    /**
     * Execute Query
     * @param sql query (string)
     * @return Resultset
     */
    public ResultSet executeQuery(String sql) {

        ArrayList<String> myArray = new ArrayList<String>();
        ResultSet rs = null;
        try {
            Statement sm = conn.createStatement();
            rs = sm.executeQuery(sql);
            if (rs == null) {
                rs.close();
                sm.close();
                return null;
            } else {
                rs.close();
                sm.close();

                return rs;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
            return null;
        }
    }

    /**
     * Execute Update Query(Insert, update, delete)
     * @param sql query (string)
     * @return int
     */
    public int executeUpdate(String sql) {
        try {
            int rs;
            Statement sm = conn.createStatement();
            rs = sm.executeUpdate(sql);
            sm.close();
            return rs;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return 0;
        }
    }
// </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="Get and Set">
    /**
     * @return the url
     */
    public String getUrl() {
        return url;
    }

    /**
     * @param url the url to set
     */
    public void setUrl(String url) {
        this.url = url;
    }

    /**
     * @return the driver
     */
    public String getDriver() {
        return driver;
    }

    /**
     * @param driver the driver to set
     */
    public void setDriver(String driver) {
        this.driver = driver;
    }

    /**
     * @return the dbname
     */
    public String getDbname() {
        return dbname;
    }

    /**
     * @param dbname the dbname to set
     */
    public void setDbname(String dbname) {
        this.dbname = dbname;
    }

    /**
     * @return the username
     */
    public String getUsername() {
        return username;
    }

    /**
     * @param username the username to set
     */
    public void setUsername(String username) {
        this.username = username;
    }

    /**
     * @return the password
     */
    public String getPassword() {
        return password;
    }

    /**
     * @param password the password to set
     */
    public void setPassword(String password) {
        this.password = password;
    }
    // </editor-fold>
}
